

'-------------------------------------------------------------
' Hands-On 14-1
'-------------------------------------------------------------

Sub ReadMe(strFileName As String)
    Dim rLine As String
    Dim i As Integer            ' line number

    i = 0

    On Error GoTo ExitHere

    Open strFileName For Input As #1

    ' stay inside the loop until the end of file is reached
        Do While Not EOF(1)
            i = i + 1
            Line Input #1, rLine
            MsgBox "Line " & i & " in " & strFileName & " reads: " _
            & Chr(13) & Chr(13) & rLine
        Loop
    MsgBox i & " lines were read."
    Close #1
    Exit Sub
ExitHere:
    MsgBox "File " & strFileName & " could not be found."
End Sub


'-------------------------------------------------------------
' Hands-On 14-2
'-------------------------------------------------------------

Sub CountChar(strFileName As String, srchChar As String)
    Dim counter As Integer
    
    counter = 0
    Open strFileName For Input As #1

    Do While Not EOF(1)
        char = Input(1, #1)
        If char = srchChar Then
          counter = counter + 1
        End If
    Loop
    If counter <> 0 Then
      MsgBox "Characters (" & srchChar & ") found: " & counter
    Else
      MsgBox "The specified character (" & srchChar & _
                ") has not been found."
    End If
    Close #1
End Sub


Sub ReadAll(strFileName As String)
    Dim all As String
    
    Open strFileName For Input As #1
    all = Input(LOF(1), #1)
    Debug.Print all
    Close #1
End Sub


'-------------------------------------------------------------
' Hands-On 14-3
'-------------------------------------------------------------

Sub WriteToTextBox(strFileName As String)
    Dim sh As Worksheet
    Set sh = ActiveWorkbook.Worksheets(3)
    
    On Error GoTo CloseFile:

    Open strFileName For Input As #1
    sh.Shapes.AddTextbox(msoTextOrientationHorizontal, _
        10, 10, 300, 200).Select

    Selection.Characters.Text = Input(LOF(1), #1)
CloseFile:
    Close #1
End Sub


'-------------------------------------------------------------
' Hands-On 14-4
'-------------------------------------------------------------

Sub Winners()
    Dim lname As String
    Dim fname As String
    Dim age As Integer

    Open "C:\Ex07_ByExample\Winners.csv" For Input As #1
    Do While Not EOF(1)
        Input #1, lname, fname, age
        MsgBox lname & ", " & fname & ", " & age
    Loop
    Close #1
End Sub


'-------------------------------------------------------------
' Hands-On 14-5
'-------------------------------------------------------------

Sub DataEntry()
    Dim lname As String
    Dim fname As String
    Dim birthdate As Date
    Dim sib As Integer

    Open "C:\Ex07_ByExample\Friends.txt" For Output As #1
    lname = "Smith"
    fname = "Gregory"
    birthdate = #1/2/1963#
    sib = 3
    Write #1, lname, fname, birthdate, sib

    lname = "Conlin"
    fname = "Janice"
    birthdate = #5/12/1948#
    sib = 1
    Write #1, lname, fname, birthdate, sib

    lname = "Kaufman"
    fname = "Steven"
    birthdate = #4/7/1957#
    sib = 0
    Write #1, lname, fname, birthdate, sib

    Close #1
End Sub


'-------------------------------------------------------------
' Hands-On 14-6
'-------------------------------------------------------------

' create a user-defined data type called Dictionary
Type Dictionary
    en As String * 16 ' English word up to 16 characters
    sp As String * 20 ' Spanish word up to 20 characters
End Type


Sub EnglishToSpanish()
    Dim d As Dictionary
    Dim recNr As Long
    Dim choice As String
    Dim totalRec As Long

    recNr = 1
    ' open the file for random access
    Open "C:\Ex07_ByExample\Translate.txt" _
        For Random As #1 Len = Len(d)

    Do
        ' get the English word
        choice = InputBox("Enter an English word", "ENGLISH")
        d.en = choice
        ' exit the loop if cancelled
        If choice = "" Then Exit Do
        choice = InputBox("Enter the Spanish equivalent of " _
            & d.en, "SPANISH EQUIVALENT  " & d.en)
        If choice = "" Then Exit Do
        d.sp = choice

        ' write to the record
        Put #1, recNr, d
        ' increase record counter
        recNr = recNr + 1
    'ask for words until Cancel
    Loop Until choice = ""
    
    totalRec = LOF(1) / Len(d)
    MsgBox "This file contains " & totalRec & " record(s)."
    ' close the file
    Close #1
End Sub


Sub VocabularyDrill()
    Dim d As Dictionary
    Dim totalRec As Long
    Dim recNr As Long
    Dim randomNr As Long
    Dim question As String
    Dim answer As String

    ' open a random access file
    Open "C:\Ex07_ByExample\Translate.txt" _
        For Random As #1 Len = Len(d)
    
    ' print the total number of bytes in this file
    Debug.Print "There are " & LOF(1) & " bytes in this file."
    
    ' find and print the total number of records
    recNr = LOF(1) / Len(d)
    Debug.Print "Total number of records: " & recNr

    Do
        ' get a random record number
        randomNr = Int(recNr * Rnd) + 1
        Debug.Print randomNr
        
        ' find the random record
        Seek #1, randomNr
        
        ' read the record
        Get #1, randomNr, d
        Debug.Print Trim(d.en); " "; Trim(d.sp)
        
        ' assign answer to a variable
        answer = InputBox("What's the Spanish equivalent?", d.en)
        
        ' finish if cancelled
        If answer = "" Then Close #1: Exit Sub
        Debug.Print answer
            ' check if the answer is correct
            If answer = Trim(d.sp) Then
                MsgBox "Congratulations!"
            Else
                MsgBox "Invalid Answer!!!"
            End If
    ' keep on asking questions until Cancel is pressed
    Loop While answer <> ""
    
    ' close file
    Close #1
End Sub


'-------------------------------------------------------------
' Hands-On 14-7
' Statements to be entered in the Immediate Window.
'-------------------------------------------------------------

    Open "C:\Ex07_ByExample\MyData.txt" For Binary As #1
    MsgBox "Total bytes: " & LOF(1)
    fname = "Julitta"                   
    Ln = Len(fname)                     
    Put #1, , Ln                   
    MsgBox "The last byte: " & Loc(1)   
    Put #1, , fname                    
    lname = "Korol"                  
    Ln = Len(lname)                  
    Put #1, , Ln 
    Put #1, , lname                     
    MsgBox "The last byte: " & Loc(1)   
    Get #1, 1, entry1                   
    MsgBox entry1                       
    Get #1, , entry2                    
    MsgBox entry2                       
    Get #1, , entry3                    
    MsgBox entry3                       
    Get #1, , entry4 
    MsgBox entry4                       
    Debug.Print entry1; entry2; entry3; entry4  
    Close #1              
